/**
 * 
 */
package com.pks.autoInsurance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.pks.autoInsurance.dto.VehicleDto;

/**
 * Implementation class for VehicleDAO.
 * 
 * @author Pankaj Soni
 * 
 */
public class VehicleDAOImpl extends AutoInsuranceDAO implements VehicleDAO {
	private static final Logger LOGGER = Logger.getLogger(VehicleDAOImpl.class);

	/**
	 * @see com.pks.autoInsurance.dao.VehicleDAO#findVehicle(java.lang.String)
	 */
	@Override
	public final VehicleDto findVehicle(final String ssn) {
		LOGGER.debug("begin");
		Connection connection = this.getConnection();
		String sql = "SELECT TYPE,MODEL,MAKE,MF_YEAR,REGNO,POLICY_TYPE,POLICY_AMOUNT,TOTAL_ACCIDENT,QUOTE_DATE FROM VEHICLE WHERE SSN=?";
		LOGGER.info("sql: " + sql);
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, ssn);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				VehicleDto vehicleDto = new VehicleDto();
				vehicleDto.setSsn(ssn);
				vehicleDto.setType(resultSet.getString("TYPE"));
				vehicleDto.setModel(resultSet.getString("MODEL"));
				vehicleDto.setMake(resultSet.getString("MAKE"));
				vehicleDto.setMfYear(resultSet.getString("MF_YEAR"));
				vehicleDto.setRegNo(resultSet.getString("REGNO"));
				vehicleDto.setPolicyType(resultSet.getString("POLICY_TYPE"));
				vehicleDto.setPolicyAmount(resultSet.getInt("POLICY_AMOUNT"));
				vehicleDto.setTotalAccident(resultSet.getInt("TOTAL_ACCIDENT"));
				vehicleDto.setQuoteDate(resultSet.getDate("QUOTE_DATE"));
				return vehicleDto;
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
			LOGGER.debug("end");
		}
		return null;
	}

	/**
	 * @see com.pks.autoInsurance.dao.VehicleDAO#updateVehicle(com.pks.autoInsurance.dto.VehicleDto)
	 */
	@Override
	public final boolean updateVehicle(final VehicleDto vehicle) {
		LOGGER.debug("begin");
		boolean result = false;
		Connection connection = this.getConnection();
		String sql = "UPDATE VEHICLE SET TYPE=?,MODEL=?,MAKE=?,"
				+ "REGNO=?,POLICY_TYPE=?,POLICY_AMOUNT=?,MF_YEAR=?,"
				+ "TOTAL_ACCIDENT=?,QUOTE_DATE=? WHERE SSN=?";
		LOGGER.info("sql: " + sql);
		try {
			connection.setAutoCommit(false);

			// update vehicle relation
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, vehicle.getType());
			preparedStatement.setString(2, vehicle.getModel());
			preparedStatement.setString(3, vehicle.getMake());
			preparedStatement.setString(4, vehicle.getRegNo());
			preparedStatement.setString(5, vehicle.getPolicyType());
			preparedStatement.setInt(6, vehicle.getPolicyAmount());
			preparedStatement.setString(7, vehicle.getMfYear());
			preparedStatement.setInt(8, vehicle.getTotalAccident());
			preparedStatement.setDate(9, new java.sql.Date(vehicle
					.getQuoteDate().getTime()));
			preparedStatement.setString(10, vehicle.getSsn());
			int rows = preparedStatement.executeUpdate();
			LOGGER.info("insertedVechileRows: " + rows);
			result = true;
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException ex) {
				LOGGER.error(e.getMessage());
			}
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
			}
		}
		LOGGER.debug("end");
		return result;

	}

}
